﻿/*
 *  phpMyAdmin.cs - .NET phpMyAdmin manager
 *
 *  Copyright (C) 2008 dreamlusion <dreamlusion@gmail.com>
 *
 *      This program is free software; you can redistribute it and/or
 *      modify it under the terms of the GNU General Public License version
 *      2 as published by the Free Software Foundation.
 *
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using System.Net;
using System.IO;
using System.Text.RegularExpressions;
using System.Web;
using System.ComponentModel;
using phpMyAdminToolkit.Common.Net;

namespace phpMyAdminToolkit.Common
{
	public sealed class phpMyAdminManager
	{
		#region Events
		public event EventHandler<phpMyAdminActivity> Activity;
		private void OnActivity(string message)
		{
			OnActivity(new phpMyAdminActivity() { Message = message });
		}

		private void OnActivity(phpMyAdminActivity phpMyAdminProgress)
		{
			if (Activity != null)
				Activity(this, phpMyAdminProgress);
		}
		#endregion

		#region Miscelaneous
		enum Compression
		{
			None,
			Zip,
			GZip
		}
		enum SqlType
		{
			Insert = 0,
			Update,
			Replace
		}
		enum SqlCompatibility
		{
			None,
			ANSI,
			DB2,
			MaxDB,
			MySQL323,
			MySQL40,
			MSSql,
			Oracle,
			Traditional
		}

		[Flags]
		enum phpMyAdminData
		{
			None = 0,
			Session = 1,
			Uri = 2,
			All = Session | Uri
		}
		#endregion

		#region Properties
		Uri _indexUri;
		Uri IndexUri
		{
			get
			{
				if (_indexUri == null)
				{
					_indexUri = new Uri(string.Concat(PmaUri.AbsoluteUri, "/index.php"));
				}

				return _indexUri;
			}
			set
			{
				if (_indexUri != value)
					_indexUri = value;
			}
		}

		phpMyAdminVersion _version;
		phpMyAdminVersion Version
		{
			get
			{
				return _version;
			}
			set
			{
				if (_version != value)
				{
					_version = value;

					if ((KeepClean & phpMyAdminData.Session) != phpMyAdminData.Session)
						DirtyData |= phpMyAdminData.Session;

					if ((KeepClean & phpMyAdminData.Uri) != phpMyAdminData.Uri)
						DirtyData |= phpMyAdminData.Uri;
				}
			}
		}

		CookieContainer SessionCookieContainer { get; set; }

		Uri _pmaUri;
		Uri PmaUri
		{
			get
			{
				return _pmaUri;
			}
			set
			{
				// w/o trimming may end up with invalid cookie -> URL association. For example
				// During login URL for user and password cookie may get set to this (invalid) URL: 
				// http://<phpmyadminBaseUri>//, and thus login will fail.

				Uri pmaUri = new Uri(value.AbsoluteUri.Trim('/'));

				if (_pmaUri != pmaUri)
				{
					_pmaUri = pmaUri;

					if ((KeepClean & phpMyAdminData.Session) != phpMyAdminData.Session)
						DirtyData |= phpMyAdminData.Session;

					if ((KeepClean & phpMyAdminData.Uri) != phpMyAdminData.Uri)
						DirtyData |= phpMyAdminData.Uri;
				}
			}
		}

		phpMyAdminData KeepClean { get; set; }
		phpMyAdminData DirtyData { get; set; }
		#endregion

		#region Methods

		void Clean()
		{
			if ((DirtyData & phpMyAdminData.Session) != phpMyAdminData.None)
			{
				SessionCookieContainer = null;
				Store = null;
				DirtyData &= ~phpMyAdminData.Session;
			}

			if ((DirtyData & phpMyAdminData.Uri) != phpMyAdminData.None)
			{
				ImportUri = ExportUri = IndexUri = null;
				DirtyData &= ~phpMyAdminData.Uri;
			}
		}

		/// <summary>
		/// </summary>
		/// <remarks>
		/// phpMyAdmin must be clean prior to calling Login.
		/// </remarks>
		public void Login(Uri pmaUri, string user, string password)
		{
			if (pmaUri == null)
				throw new ArgumentNullException("Parameter is required.", "pmaUri");

			if (string.IsNullOrEmpty(user))
				throw new ArgumentException("Parameter is required.", "user");

			if (string.IsNullOrEmpty(password))
				throw new ArgumentException("Parameter is required.", "password");

			KeepClean |= phpMyAdminData.All;

			PmaUri = pmaUri;
			Uri indexUri = IndexUri;

			CookieContainer cookies = new CookieContainer();

			// Prepare login params.
			List<Param> loginParams = new List<Param>();
			loginParams.Add("pma_username", user);
			loginParams.Add("pma_password", password);
			byte[] loginParamsData = loginParams.ConvertToUrlEncodedParamList();

			// Prepare login request.
			HttpWebRequest loginRequest = WebRequest.Create(indexUri) as HttpWebRequest;
			loginRequest.Method = "POST";
			loginRequest.ContentType = "application/x-www-form-urlencoded";
			loginRequest.ContentLength = loginParamsData.Length;
			loginRequest.CookieContainer = cookies;
			using (Stream loginRequestStream = loginRequest.GetRequestStream())
			{
				loginRequestStream.Write(loginParamsData, 0, loginParamsData.Length);
			}

			OnActivity(string.Format("Logging in {0}...", indexUri));
			HttpWebResponse loginResponse = loginRequest.GetResponse() as HttpWebResponse;

			// Version Detection. 
			// NOTE: Version must exist in index.php title, at least after a successfull login. If Version detection fails,
			// it may be due to failed login.
			string loginResponseText = string.Empty;
			using (Stream s = loginResponse.GetResponseStream())
			using (StreamReader sr = new StreamReader(s))
			{
				loginResponseText = sr.ReadToEnd();
			}

			Regex regex = new Regex(phpMyAdminToolkit.Common.Properties.Resources.VersionRegex, RegexOptions.Singleline);
			try
			{
				Version = phpMyAdminVersion.Create(regex.Matches(loginResponseText).Cast<Match>().Single().Groups["Version"].Value);
			}
			catch
			{
				throw new Exception("phpMyAdmin version cannot be automatically detected. Check your login credentials and try again. If the problem persists, create a new work item here: http://www.codeplex.com/phpMyAdminToolkit/WorkItem/List.aspx.");
			}

			// Detect successful login. If the cookie has not been deleted, it should not exist in response.
			Cookie passwordCookie = loginResponse.Cookies
				.Cast<Cookie>()
				.FirstOrDefault(c => c.Name.StartsWith(PasswordCookieName, StringComparison.OrdinalIgnoreCase));

			if (passwordCookie != null && string.Compare(passwordCookie.Value, "deleted", true) == 0)
			{
				throw new Exception(string.Format("Login failed. {0} deleted.", passwordCookie.Name));
			}

			/* 
			 * HACK: pma_cookie_host did not get set correctly (Version 2.6.4-pl4), making it available only to index.php
			 * Set-Cookie: pma_cookie_host=fdb1.runhosting.com; expires=Fri, 29 Aug 2008 22:16:59 GMT
			 * Set-Cookie: pma_cookie_username-1=YW602Un1WmdOWI1pYUFhEAuhFaFv%2Fbai; expires=Fri, 29 Aug 2008 22:16:59 GMT; path=/phpMyAdmin/
			 * Set-Cookie: pma_cookie_password-1=Z6%2Fpc47NMms%3D; path=/phpMyAdmin/ 
			 */
			if (Version < phpMyAdminVersion.Create("2.7") && Version >= phpMyAdminVersion.Create("2.6"))
			{
				CookieContainer hackedCookies = new CookieContainer();
				foreach (Cookie c in cookies.GetCookies(indexUri))
				{
					c.Path = PmaUri.AbsolutePath;
					hackedCookies.Add(c);
				}
				cookies = hackedCookies;
			}

			// We have a session ;)
			SessionCookieContainer = cookies;
			KeepClean &= ~phpMyAdminData.All;

			OnActivity("Success.");

			if (Version >= phpMyAdminVersion.Create("2.11") && Version < phpMyAdminVersion.Create("2.12"))
			{
				// Token detection. Version 2.11.8 needs this for operations other than login.

				// Prefer finding the URL parameters from the response HTML (from a javascript definition) rather than
				// using the response URL which may not contain the parameters.
				Regex query = new Regex(@"var\s+common_query\s*?=\s*?(?<quote>['""])(?<params>.*?)\k<quote>\s*?;"); // "var common_query = '...';"
				String queryText = query.Match(loginResponseText).Groups["params"].Value;
				if (string.IsNullOrEmpty(queryText))
					queryText = loginResponse.ResponseUri.Query;
				
				List<Param> queryParams = new List<Param>();
				foreach (string pair in queryText.Split('&'))
				{
					string[] pairExpanded = pair.Split(new char[] { '=' });
					queryParams.Add(HttpUtility.UrlDecode(pairExpanded[0]),
						pairExpanded.Length > 1 ? HttpUtility.UrlDecode(pairExpanded[1]) : string.Empty);
				}

				Param paramToken = queryParams.Single(p => String.Compare(p.Name, Token, true) == 0);
				Store.Add(Token, paramToken.Value);
			}
		}

		/// <summary>
		/// Exports a backup of the specified database to the specified backup directory.
		/// </summary>
		/// <param name="databaseName">The name of the database.</param>
		/// <param name="backupDirectory">The directory where the backup will be stored.</param>
		/// <param name="includedTables">The regex that match the names of the tables to include in the backup (null/empty means all tables).</param>
		/// <param name="excludedTables">The regex that match the names of the tables to exclude from the backup. If a table is specified by
		/// both the included and excluded table lists, it will be excluded.</param>
		public void ExportDatabase(string databaseName, string backupDirectory, string includedTables, string excludedTables)
		{
			if (string.IsNullOrEmpty(databaseName))
				throw new ArgumentException("Parameter is required.", "databaseName");

			if (string.IsNullOrEmpty(backupDirectory))
				throw new ArgumentException("Parameter is required.", "backupDirectory");
			
			// NOTE: Samples in Export.txt

			// Prepare export params.
			int excludedTablesCount = 0;

			List<string> tables = GetDatabaseDetails(databaseName).Tables
				.Where(table =>
				{
					bool include = (string.IsNullOrEmpty(includedTables) || Regex.IsMatch(table, includedTables)) &&
						(string.IsNullOrEmpty(excludedTables) || !Regex.IsMatch(table, excludedTables));
					
					if (!include)
						excludedTablesCount++;
					
					return include;
				}).ToList(); // We need to enumerate exactly once so that the counters update correctly.

			// If no table remains to be backed up, say it and return.
			if (tables.Count == 0)
			{
				OnActivity("The database was not exported because all tables were excluded.");
				return;
			}

			OnActivity(string.Format("Including {0} table(s).", tables.Count));
			if (excludedTablesCount > 0)
				OnActivity(string.Format("Excluding {0} table(s).", excludedTablesCount));

			string filenameTemplate = string.Format("__DB__-{0}", DateTime.Now.Ticks);
			byte[] exportParamsData = GetSqlExportParams(databaseName, tables, SqlType.Insert, string.Empty, SqlCompatibility.None, filenameTemplate, Compression.GZip, null, null, null, null, null, null, null).ConvertToUrlEncodedParamList();

			// Prepare export request.
			HttpWebRequest exportRequest = WebRequest.Create(ExportUri) as HttpWebRequest;
			exportRequest.Method = "POST";
			exportRequest.ContentType = "application/x-www-form-urlencoded";
			exportRequest.ContentLength = exportParamsData.Length;
			exportRequest.CookieContainer = SessionCookieContainer;
			using (Stream exportRequestStream = exportRequest.GetRequestStream())
			{
				exportRequestStream.Write(exportParamsData, 0, exportParamsData.Length);
			}

			// Perform the Download.
			FileDownload download = new FileDownload()
			{
				Request = exportRequest,
			};

			OnActivity("Downloading database backup...");
			//int counter = 0;
			//download.ProgressChanged += delegate(object sender, ProgressChangedEventArgs e)
			//{
			//    counter++;
			//    switch (counter % 4)
			//    {
			//        case 0: Console.Write("/"); break;
			//        case 1: Console.Write("-"); break;
			//        case 2: Console.Write("\\"); break;
			//        case 3: Console.Write("-"); break;
			//    }
			//    Console.SetCursorPosition(Console.CursorLeft - 1, Console.CursorTop);
			//};

			string completeFilePath = download.Do(backupDirectory, null, null);
			OnActivity(string.Format("Backup download complete in {0}", completeFilePath));
		}

		DatabaseDetails GetDatabaseDetails(string databaseName)
		{
			if (string.IsNullOrEmpty(databaseName))
				throw new ArgumentException("Parameter is required.", "databaseName");

			// Get available tables.
			HttpWebRequest detailsRequest = WebRequest.Create(GetDetailsUri(databaseName)) as HttpWebRequest;
			detailsRequest.CookieContainer = SessionCookieContainer;
			TextDownload download = new TextDownload()
			{
				Request = detailsRequest,
			};

			OnActivity("Fetching available tables...");
			string details = download.Do();

			Regex regex = new Regex(phpMyAdminToolkit.Common.Properties.Resources.TableNamesRegex, RegexOptions.Singleline);

			// There should only be one match.
			Match match = regex.Matches(details).Cast<Match>().Single();

			IEnumerable<string> tables = match.Groups["TableNames"].Captures.Cast<Capture>()
				.Select(c => c.Value);

			OnActivity(string.Format("Found {0} table(s).", tables.Count()));

			return new DatabaseDetails()
			{
				Tables = tables
			};
		}

		public void ExecuteSqlCommand(string sqlCommand, string databaseName, string delimiter)
		{
			if (string.IsNullOrEmpty(sqlCommand))
				throw new ArgumentException("Parameter is required.", "sqlCommand");

			if (string.IsNullOrEmpty(databaseName))
				throw new ArgumentException("Parameter is required.", "databaseName");

			// NOTE: Samples in Import.txt

			if (string.IsNullOrEmpty(sqlCommand))
				throw new ArgumentException();

			if (string.IsNullOrEmpty(databaseName))
				throw new ArgumentException();

			// Execute Sql Command, if one provided.
			List<Param> importParams = new List<Param>();
			importParams.Add("db", databaseName);
			importParams.Add("sql_query", sqlCommand);
			importParams.Add("sql_delimiter", string.IsNullOrEmpty(delimiter) ? ";" : delimiter);
			importParams.Add("is_js_confirmed", "1");

			string boundary = (new Random().Next(int.MaxValue / 2, int.MaxValue)).ToString();
			byte[] importParamsData = importParams.ConvertToMultipartFormData(boundary);

			OnActivity(string.Format("Executing sql command \"{0}\"...", sqlCommand));
			
			HttpWebRequest importRequest = WebRequest.Create(ImportUri) as HttpWebRequest;
			importRequest.Method = "POST";
			importRequest.ContentType = string.Concat("multipart/form-data; boundary=", boundary);
			importRequest.ContentLength = importParamsData.Length;
			importRequest.CookieContainer = SessionCookieContainer;
			using (Stream importRequestStream = importRequest.GetRequestStream())
			{
				importRequestStream.Write(importParamsData, 0, importParamsData.Length);
			}

			HttpWebResponse cmdResponse = importRequest.GetResponse() as HttpWebResponse;

			OnActivity("Sql command execution completed succesfully.");
		}
		#endregion

		#region phpMyAdmin Version Specific
		Uri GetDetailsUri(string databaseName)
		{
			if (string.IsNullOrEmpty(databaseName))
				throw new ArgumentException("Parameter is required.", "databaseName");

			Uri detailsUri = null;
			if (Version <= phpMyAdminVersion.Create("2.6.4"))
			{
				detailsUri = new Uri(string.Format("{0}/db_details_export.php?db={1}", PmaUri.AbsoluteUri, databaseName));
			}
			else
			{
				detailsUri = new Uri(string.Format("{0}/db_export.php?db={1}", PmaUri.AbsoluteUri, databaseName));
			}

			return detailsUri;
		}

		Uri _importUri;
		Uri ImportUri
		{
			get
			{
				if (_importUri == null)
				{
					if (Version <= phpMyAdminVersion.Create("2.6.4"))
					{
						_importUri = new Uri(string.Concat(PmaUri.AbsoluteUri, "/read_dump.php"));
					}
					else
					{
						_importUri = new Uri(string.Concat(PmaUri.AbsoluteUri, "/import.php?token=", Store[Token]));
					}
				}

				return _importUri;
			}
			set
			{
				if (_importUri != value)
					_importUri = value;
			}
		}

		Uri _exportUri;
		Uri ExportUri
		{
			get
			{
				if (_exportUri == null)
				{
					if (Version <= phpMyAdminVersion.Create("2.6.4"))
					{
						_exportUri = new Uri(string.Concat(PmaUri.AbsoluteUri, "/export.php"));
					}
					else
					{
						_exportUri = new Uri(string.Concat(PmaUri.AbsoluteUri, "/export.php?token=", Store[Token]));
					}
				}
				return _exportUri;
			}
			set
			{
				if (_exportUri != value)
					_exportUri = value;
			}
		}

		List<Param> GetSqlExportParams
			(string databaseName, IEnumerable<string> tables, SqlType? sqlType, string headerComment,
			SqlCompatibility? sqlCompatibility, string filenameTemplate, Compression? compression,
			bool? ifNotExists, bool? autoIncrement, bool? showColumns, bool? extendedInsert, bool? useBackquotes,
			bool? hexForBinary, int? maxQuerySize)
		{
			if (string.IsNullOrEmpty(filenameTemplate))
				throw new ArgumentException("Parameter is required.", "filenameTemplate");

			if (string.IsNullOrEmpty(databaseName))
				throw new ArgumentException("Parameter is required.", "databaseName");

			if (tables == null || tables.FirstOrDefault() == null)
				throw new ArgumentException("A collection of tables to export is required.", "tables");

			List<Param> exportParams = new List<Param>();

			// Version independant parameters.
			exportParams.Add("db", databaseName);
			exportParams.Add("export_type", "database");
			exportParams.Add("what", "sql");
			exportParams.Add("filename_template", filenameTemplate);
			exportParams.Add("asfile", "sendit");
			exportParams.Add("compression", (compression.HasValue) ? compression.Value.ToString() : "none");
			exportParams.Add("sql_structure", "structure"); // exportParams.Add("sql_structure", "something");
			exportParams.Add("sql_data", "data"); // exportParams.Add("sql_data", "something");

			foreach (string table in tables)
			{
				exportParams.Add("table_select[]", table);
			}

			if (Version <= phpMyAdminVersion.Create("2.6.4"))
			{
				exportParams.Add("sql_compat", (sqlCompatibility.HasValue) ? sqlCompatibility.Value.ToString().ToUpper() : "NONE");
				exportParams.Add("header_comment", headerComment);
				exportParams.Add("if_not_exists", (!ifNotExists.HasValue || ifNotExists.Value) ? "1" : "0");
				exportParams.Add("auto_increment", (!autoIncrement.HasValue || autoIncrement.Value) ? "1" : "0");
				exportParams.Add("use_backquotes", (!useBackquotes.HasValue || useBackquotes.Value) ? "1" : "0");
				exportParams.Add("showcolumns", (!showColumns.HasValue || showColumns.Value) ? "true" : "false");
				exportParams.Add("extended_ins", (!extendedInsert.HasValue || extendedInsert.Value) ? "true" : "false");
				exportParams.Add("hexforbinary", (!hexForBinary.HasValue || hexForBinary.Value) ? "true" : "false");
				exportParams.Add("sql_type", sqlType.ToString().ToLower());
			}
			else
			{
				exportParams.Add("sql_compatibility", (sqlCompatibility.HasValue) ? sqlCompatibility.Value.ToString().ToUpper() : "NONE");
				exportParams.Add("sql_header_comment", headerComment);
				exportParams.Add("sql_if_not_exists", (!ifNotExists.HasValue || ifNotExists.Value) ? "something" : string.Empty);
				exportParams.Add("sql_auto_increment", (!autoIncrement.HasValue || autoIncrement.Value) ? "something" : string.Empty);
				exportParams.Add("sql_backquotes", (!useBackquotes.HasValue || useBackquotes.Value) ? "something" : string.Empty);
				exportParams.Add("sql_columns", (!showColumns.HasValue || showColumns.Value) ? "something" : string.Empty);
				exportParams.Add("sql_extended", (!extendedInsert.HasValue || extendedInsert.Value) ? "something" : string.Empty);
				exportParams.Add("sql_hex_for_blob", (!hexForBinary.HasValue || hexForBinary.Value) ? "something" : string.Empty);
				exportParams.Add("sql_type", ((int)sqlType).ToString());

				if (maxQuerySize.HasValue)
				{
					if (maxQuerySize.Value <= 0)
						throw new ArgumentOutOfRangeException("Invalid range for the max query size argument. Valid ranges are > 0.");

					exportParams.Add("sql_max_query_size", maxQuerySize.Value.ToString());
				}
				else
				{
					exportParams.Add("sql_max_query_size", "50000");
				}

			}

			return exportParams;
		}

		string _passwordCookieName;
		public string PasswordCookieName
		{
			get
			{
				if (string.IsNullOrEmpty(_passwordCookieName))
				{
					if (Version >= phpMyAdminVersion.Create("2.11") && Version < phpMyAdminVersion.Create("2.12")) 
					{
						_passwordCookieName = "pmaPass";
					}
					else if (Version >= phpMyAdminVersion.Create("2.6") && Version < phpMyAdminVersion.Create("2.7"))
					{
						_passwordCookieName = "pma_cookie_pass";
					}
				}

				return _passwordCookieName;
			}
		}

		public const string Token = "token";

		NameValueCollection _store;
		NameValueCollection Store
		{
			get
			{
				if (_store == null)
				{
					NameValueCollection store = new NameValueCollection();
					_store = store;
				}

				return _store;
			}
			set
			{
				_store = value;
			}
		}
		#endregion
	}


}
